package com.sport.setting.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sport.model.application.pojos.SportMemberEnroll;
import com.sport.model.user.bos.AccountUserBo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface SportMemberEnrollMapper extends BaseMapper<SportMemberEnroll> {

    @Select("SELECT se.sport_member_grade,se.sport_member_gender,"
    + "SUBSTRING_INDEX(SUBSTRING_INDEX(se.sport_member_project,',',n),',',-1)AS sport_member_project_id,"
    + "(SELECT GROUP_CONCAT(se2.id SEPARATOR ',') FROM sport_member_enroll se2 "
    + "WHERE se2.school_tenement_id=#{schoolTenementId} "
    + "AND se2.sport_member_grade=se.sport_member_grade "
    + "AND se2.sport_member_gender=se.sport_member_gender "
    + "AND FIND_IN_SET(sport_member_project_id,se2.sport_member_project)>0) AS id_list, "
    + "COUNT(*) AS count "
    + "FROM sport_member_enroll se "
    + "CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS numbers "
    + "WHERE school_tenement_id = #{schoolTenementId} and sport_binding_school=#{sportBindingSchool} and "
    + "sport_member_competition_status=#{sportMemberCompetitionStatus} "
    + "GROUP BY sport_member_grade,sport_member_gender,sport_member_project_id")
    List<Map<String,Object>> countByGroup(@Param("schoolTenementId")String schoolTenementId,@Param("sportBindingSchool")Long sportBindingSchool,@Param("sportMemberCompetitionStatus") Integer sportMemberCompetitionStatus);



    @Select("SELECT sport_member_unit,sport_member_grade,"
            +"MIN(CASE WHEN sport_member_gender = 1 THEN sport_member_num  ELSE 0 END) AS min_sport_member_male_num,"
            +"MAX(CASE WHEN sport_member_gender = 1 THEN sport_member_num  ELSE 0 END) AS max_sport_member_male_num,"
            +"MIN(CASE WHEN sport_member_gender = 2 THEN sport_member_num  ELSE 0 END) AS min_sport_member_female_num,"
            +"MAX(CASE WHEN sport_member_gender = 2 THEN sport_member_num  ELSE 0 END) AS max_sport_member_female_num,"
            +"SUM(CASE WHEN sport_member_gender = 1 THEN  1 ELSE 0 END) AS maleCount,"
            +"SUM(CASE WHEN sport_member_gender = 2 THEN  1 ELSE 0 END) AS femaleCount "
            +"FROM sport_member_enroll "
            +"GROUP BY sport_member_grade, sport_member_unit")
    List<Map<String,Object>> countBySportMemberUnitMaps(@Param("schoolTenementId")String schoolTenementId);

    @Select("SELECT sport_member_grade,"
            +"SUM(CASE WHEN sport_member_gender = 1 THEN 1 ELSE 0 END) AS maleTotalCount,"
            +"SUM(CASE WHEN sport_member_gender = 2 THEN 1 ELSE 0 END) AS femaleTotalCount "
            +"FROM sport_member_enroll "
            +"GROUP BY sport_member_grade")
    List<Map<String,Object>> countNumByGrade(@Param("schoolTenementId")String schoolTenementId);



}
